﻿/* 
 * 
 * Copyright 2013 Bendsoft. All rights reserved.
 * http://www.bendsoft.com
 * 
 * This code is distributed under the New BSD License (BSD).
 * 
 * @package     Camelot SharePoint Integration Library
 * @codeplex    http://camelottoolkit.codeplex.com
 * @license     New BSD License (BSD)
 * 
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;
using System.Globalization;
using Camelot.SharePointIntegration.Client;

namespace Camelot.SharePointIntegration
{
    public static class MySql
    {
        private static string CreateTypeDeclaration(Type type, DataRow schemaRow)
        {
            Int32 maxLength = -1;
            Int32 decimals = -1;
            bool primaryKey = false;

            if (schemaRow != null)
            {
                if (!Convert.IsDBNull(schemaRow["MaxLength"]))
                    maxLength = (Int32)schemaRow["MaxLength"];

                if (!Convert.IsDBNull(schemaRow["Decimals"]))
                    decimals = (Int32)schemaRow["Decimals"];

                if (!Convert.IsDBNull(schemaRow["PrimaryKey"]))
                    primaryKey = (bool)schemaRow["PrimaryKey"];
            }

            return CreateTypeDeclaration(type, maxLength, decimals, primaryKey);
        }

        private static string CreateTypeDeclaration(Type type, Int32 maxLength, Int32 decimals, bool primaryKey)
        {
            string sqlType = "text";

            if (type == typeof(Guid))
                sqlType = "varchar(36)";

            if (type == typeof(string))
                if (maxLength > -1 && maxLength < 256)
                    sqlType = "varchar(" + maxLength + ")";
                else
                    sqlType = "text";

            if (type == typeof(DateTime))
                sqlType = "datetime";

            if (type == typeof(bool))
                sqlType = "boolean";

            if (type == typeof(Int32))
                sqlType = "int(11)";

            if (type == typeof(double))
                if (decimals == 0)
                    sqlType = "int(11)";
                else
                    sqlType = "double";

            if (type == typeof(byte[]))
                sqlType = "mediumblob";

            if (primaryKey)
                return sqlType + " PRIMARY KEY";
            else
                return sqlType;
        }

        private static string Escape(string value)
        {
            if (!string.IsNullOrEmpty(value))
                return Regex.Replace(value, @"[\r\n\x00\x1a\\'""]", @"\$0");
            else
                return value;
        }

        public static String CreateMySqlDump(string databaseName, ListData data)
        {
            if (data.Description == null)
                throw new Exception("ListData description is null or empty");

            if (data.Schema == null)
                throw new Exception("ListData does not contain schema");

            if (data.Rows == null)
                throw new Exception("ListData does not contain rows");

            var dumpBuilder = new StringBuilder();

            dumpBuilder.AppendLine("-- SharePoint MySQL dump");
            dumpBuilder.AppendLine(string.Format("-- Dump created: {0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
            dumpBuilder.AppendLine();

            dumpBuilder.AppendLine("--");
            dumpBuilder.AppendLine(string.Format("-- Using database `{0}`", databaseName));
            dumpBuilder.AppendLine("--");
            dumpBuilder.AppendLine();

            dumpBuilder.AppendLine(string.Format("CREATE DATABASE IF NOT EXISTS `{0}`;", databaseName));
            dumpBuilder.AppendLine(string.Format("USE `{0}`;", databaseName));
            dumpBuilder.AppendLine();

            dumpBuilder.AppendLine("--");
            dumpBuilder.AppendLine(string.Format("-- Table structure for table `{0}`", data.Description.Title));
            dumpBuilder.AppendLine("--");
            dumpBuilder.AppendLine();

            dumpBuilder.AppendLine(string.Format("DROP TABLE IF EXISTS `{0}`;", data.Description.Title));
            dumpBuilder.AppendLine(string.Format("CREATE TABLE `{0}` (", data.Description.Title));

            var rows = data.Schema.AsEnumerable().Where(m => (bool)m["VisibleInView"]);
            Int32 rowIndex = 1;
            foreach (DataRow schemaRow in rows)
            {
                var columnName = (string)schemaRow["DecodedName"];
                
                dumpBuilder.Append("`");
                dumpBuilder.Append(columnName);
                dumpBuilder.Append("` ");

                if (data.Rows.Columns.Contains(columnName))
                {
                    var sqlType = CreateTypeDeclaration(data.Rows.Columns[columnName].DataType, schemaRow);
                    dumpBuilder.Append(sqlType);
                }
                else
                {
                    throw new Exception(string.Format("Schema column missing in row columns: {0}", columnName));
                }

                if (rowIndex < rows.Count())
                    dumpBuilder.AppendLine(",");
                else
                    dumpBuilder.AppendLine();

                rowIndex += 1;
            }

            dumpBuilder.AppendLine(") ENGINE=InnoDB DEFAULT CHARSET=latin1;");
            dumpBuilder.AppendLine();

            dumpBuilder.AppendLine("--");
            dumpBuilder.AppendLine(string.Format("-- Dumping data for table `{0}`", data.Description.Title));
            dumpBuilder.AppendLine("--");
            dumpBuilder.AppendLine();

            dumpBuilder.AppendLine(String.Format("LOCK TABLES `{0}` WRITE;", data.Description.Title));

            foreach (DataRow row in data.Rows.Rows)
            {
                dumpBuilder.AppendLine(String.Format("INSERT INTO `{0}` SET ", data.Description.Title));

                Int32 columnIndex = 1;
                foreach (DataColumn column in data.Rows.Columns)
                {
                    dumpBuilder.Append(string.Format(" `{0}` = ", column.ColumnName));

                    if (row[column] == null || Convert.IsDBNull(row[column]))
                    {
                        dumpBuilder.Append("NULL");

                    } else if (row[column] is string) {
                            dumpBuilder.Append(string.Format("'{0}'", Escape((string)row[column])));

                    } else if (row[column] is DateTime) {
                            dumpBuilder.Append(string.Format("'{0}'", ((DateTime)row[column]).ToString("yyyy-MM-dd HH:mm:ss")));

                    } else if (row[column] is bool) {
                            dumpBuilder.Append(((bool)row[column]) ? "1" : "0");

                    } else if (row[column] is Guid) {
                            dumpBuilder.Append(string.Format("'{0}'", ((Guid)row[column]).ToString()));

                    } else if (row[column] is Int32) {
                            dumpBuilder.Append((Int32)row[column]);

                    } else if (row[column] is double) {
                            dumpBuilder.Append(((double)row[column]).ToString(CultureInfo.GetCultureInfo("en-US").NumberFormat));

                    } else if (row[column] is Array && !(row[column] is byte[])) {
                        if (((Array)row[column]).Length == 0)
                        {
                            dumpBuilder.Append("NULL");
                        } else {
                            dumpBuilder.Append("'");
                            Int32 arrayIndex = 1;
                            foreach (var arrayValue in (Array)row[column])
                            {
                                dumpBuilder.Append(Escape(arrayValue.ToString()));
                                
                                if (arrayIndex < ((Array)row[column]).Length)
                                    dumpBuilder.Append(",");

                                arrayIndex += 1;
                            }
                            dumpBuilder.Append("'");
                        }

                    } else {
                        throw new Exception(string.Format("System type not supported: {0}", row[column].GetType().Name));
                    }

                    if (columnIndex < data.Rows.Columns.Count)
                        dumpBuilder.AppendLine(",");
                    
                    columnIndex += 1;
                }

                dumpBuilder.AppendLine(";");
            }

            dumpBuilder.AppendLine("UNLOCK TABLES;");
            return dumpBuilder.ToString();
        }
    }
}
